package com.aceleeyy.action;

import com.aceleeyy.common.web.model.ExtendSource;
import com.aceleeyy.common.web.model.SqlInfoVO;
import com.aceleeyy.datasource.DynamicDataSourceContextHolder;
import com.aceleeyy.datasource.DynamicRoutingDataSource;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.*;

@Slf4j
public abstract class AbstractSqlQueryStrategy implements DataBaseQuery {

	@Autowired
	@Qualifier("dynamicDataSource")
	private DynamicRoutingDataSource dynamicDataSource;

	protected static final String JDBC_URL = "jdbcUrl";
	protected static final String USERNAME = "userName";
	protected static final String PASSWORD = "password";

	protected abstract String getDriverName();

	@Override
	public Optional<Integer> executeDML(ExtendSource extendSource, String sql){
		/**
		 * 获取指定数据源
		 */
		SqlInfoVO sqlInfoVO = createSqlInfoVO(extendSource);
		DynamicDataSourceContextHolder.setDataSourceKey(sqlInfoVO.getProjectId());
		if(!DynamicRoutingDataSource.isExistDataSource(sqlInfoVO.getProjectId())) {
			log.warn("sql增删改错误：数据源不存在");
			return Optional.empty();
		}

		int result = jdbcDML(sql);
		return Optional.of(result);
	}

	private int jdbcDML(String sql) {
		int rowDatas = 0;
		Statement stmt;
		Connection conn = null;
		try {
			conn = dynamicDataSource.getConnection();
			if (conn.isClosed()) {
				return rowDatas;
			}
			stmt = conn.createStatement();
			rowDatas = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			log.error("sql增删改异常：{}",e.getMessage());
		} finally {
			DynamicDataSourceContextHolder.clearDataSourceKey();
		}
		return rowDatas;
	}

	@Override
	public Optional<List<Map<String,Object>>> query(ExtendSource extendSource, String sql){
		/**
		 * 获取指定数据源，如果获取不到，则add
		 */
		SqlInfoVO sqlInfoVO = createSqlInfoVO(extendSource);
		DynamicDataSourceContextHolder.setDataSourceKey(sqlInfoVO.getProjectId());
		if(!DynamicRoutingDataSource.isExistDataSource(sqlInfoVO.getProjectId())) {
			dynamicDataSource.addDataSource(sqlInfoVO);
		}

		List<Map<String,Object>> resultMap = jdbcQuery(sql);
		if(CollectionUtils.isEmpty(resultMap)) {
			return Optional.empty();
		}
		return Optional.of(resultMap);
	}

	private List<Map<String,Object>> jdbcQuery(String sql) {
		List<Map<String,Object>> rowDatas = new ArrayList<>();
		Statement stmt;
		Connection conn = null;
		try {
			conn = dynamicDataSource.getConnection();
			if (conn.isClosed()) {
				return rowDatas;
			}

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData();
			int columCount = md.getColumnCount();

			while(rs.next()) {
				Map<String,Object> rowData = new HashMap<>();
				for(int i = 1; i <= columCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				rowDatas.add(rowData);
			}
		} catch (SQLException e) {
			log.error("sql查询错误：{}",e.getMessage());
		} finally {
			DynamicDataSourceContextHolder.clearDataSourceKey();
		}
		return rowDatas;
	}

	@Override
	public Optional<Map<String,Object>> querys(ExtendSource extendSource, Map<String, String> sqls){
		if(null==sqls || sqls.isEmpty()){
			return Optional.empty();
		}

		/**
		 * 获取指定数据源，如果获取不到，则add
		 */
		SqlInfoVO sqlInfoVO = createSqlInfoVO(extendSource);
		DynamicDataSourceContextHolder.setDataSourceKey(sqlInfoVO.getProjectId());
		if(!DynamicRoutingDataSource.isExistDataSource(sqlInfoVO.getProjectId())) {
			dynamicDataSource.addDataSource(sqlInfoVO);
		}

		Map<String,Object> resultMap = jdbcQuerys(sqls);
		if(CollectionUtils.isEmpty(resultMap)) {
			return Optional.empty();
		}
		return Optional.of(resultMap);
	}

	private Map<String,Object> jdbcQuerys(Map<String, String> sqls) {
		Map<String,Object> result = Maps.newHashMap();

		Statement stmt;
		Connection conn = null;
		try {
			conn = dynamicDataSource.getConnection();
			if (conn.isClosed()) {
				return result;
			}

			stmt = conn.createStatement();

			String sql = null;
			List<Map<String,Object>> rowDatas = null;
			for (Map.Entry<String, String> entry : sqls.entrySet()){
				sql = entry.getValue();

				rowDatas = new ArrayList<>();
				ResultSet rs = stmt.executeQuery(sql);
				ResultSetMetaData md = rs.getMetaData();
				int columCount = md.getColumnCount();

				while(rs.next()) {
					Map<String,Object> rowData = new HashMap<>();
					for(int i = 1; i <= columCount; i++) {
						rowData.put(md.getColumnName(i), rs.getObject(i));
					}
					rowDatas.add(rowData);
				}

				result.put(entry.getKey(),rowDatas);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			DynamicDataSourceContextHolder.clearDataSourceKey();
		}
		return result;
	}

	private SqlInfoVO createSqlInfoVO(ExtendSource extendSource) {
		String connectionInfo = extendSource.getConnectionInfo();
		SqlInfoVO sqlInfoVO = new SqlInfoVO();
		sqlInfoVO.setDriverClassName(getDriverName());
		JSONObject jsonObject = JSON.parseObject(connectionInfo);
		sqlInfoVO.setUrl(jsonObject.getString(JDBC_URL));
		sqlInfoVO.setPassword(jsonObject.getString(PASSWORD));
		sqlInfoVO.setUsername(jsonObject.getString(USERNAME));
		sqlInfoVO.setProjectId(extendSource.getDbType() + extendSource.getId());
		return sqlInfoVO;
	}

}
